__author__ = 'zoulida'

import pymysql
import pandas as pd
import akshare as ak

def queryMySQL__test():
    ###########################查询刚才操作的成果##################################

    # 重新建立数据库连接
    db = pymysql.connect(host='localhost', user = 'root', password = "root", charset='utf8')
    cursor = db.cursor()
    sqlSentence2 = "use stockDataBase;"
    cursor.execute(sqlSentence2)
    # 查询数据库并打印内容
    cursor.execute('select * from stock_600519')
    results = cursor.fetchall()
    '''for row in results:
        print(row)'''
    rows=pd.DataFrame(results)
    rows.columns = ['num','日期','股票代码','名称','收盘价','最高价','最低价','开盘价','前收盘','涨跌额','涨跌幅','换手率','成交量','成交金额','总市值','流通市值' ]
    print (rows)
    print(rows.loc[0,'日期'])
    # 关闭
    cursor.close()
    db.commit()
    db.close()

def rangeQuery(start = '2021-01-01', end = '2022-01-01', codebase = "stock_600519"):
    from tools import connectMySQL
    cursor = connectMySQL.getCursor('stockDataBase')
    sqlStan = "select * from %s where 日期 between '%s' and '%s'" %(codebase, start, end)
    #print(sqlStan)
    cursor.execute(sqlStan)
    results = cursor.fetchall()
    rows=pd.DataFrame(results)
    if len(rows.index)!=0 :
        rows.columns = ['num','日期','股票代码','名称','收盘价','最高价','最低价','开盘价','前收盘','涨跌额','涨跌幅','换手率','成交量','成交金额','总市值','流通市值' ]
    #print(rows)
    return rows

def fastIncrease(rows, zhangfu = 20):
    for index, row in rows.iterrows():
        #print(index, row) # 输出每行的索引值
        #print( row.loc["收盘价"])
        end = index + 10
        if end > rows.shape[0] - 1:
            end = rows.shape[0] - 1
        if row.loc["收盘价"] == 0:
            continue
        if (rows.loc[end,'收盘价'] - row.loc["收盘价"]) /row.loc["收盘价"] * 100 > zhangfu:
            inc = (rows.loc[end,'收盘价'] - row.loc["收盘价"]) /row.loc["收盘价"] * 100


            from tools import MarketTime
            mtime = MarketTime.getMarketTime(row.loc["股票代码"])
            #print(mtime)
            print(row.loc["日期"],mtime,inc, row.loc["名称"], row.loc["股票代码"])
            liststock = [row.loc["日期"],mtime,inc, row.loc["名称"], row.loc["股票代码"]]
            df.loc[len(df)] = liststock
def get_all_stock2():
    from tools import StockList
    return StockList.get_all_stock2()

global df
df = pd.DataFrame(columns=['日期','上市日期','涨幅','名称','股票代码'])

def getYaogu(start = '2021-01-01', end = '2022-01-01'):
    codelist = get_all_stock2()



    for index, row in codelist.iterrows():
        print(index, row)
        basename = 'stock_' + row.loc['symbol']
        try:
            rows = rangeQuery(start = start, end = end, codebase = basename)
            fastIncrease(rows, 50)
        except Exception as e:
            import traceback
            print('traceback.print_exc():', traceback.print_exc())
            #logger.info(e)


    print(df)
    filepath = 'D:\\zhangfu50-1.csv'
    df.to_csv(filepath, encoding="gbk", index=True)

if __name__ == '__main__':
    getYaogu()
    #rows = rangeQuery(codebase = 'stock_000301')
    #fastIncrease(rows,20)